<?php
error_reporting(E_ALL ^ E_NOTICE);
set_time_limit(0);
$db_name = "dinocap2";

$table_name = "dinocap2hd";

$connection = @mysql_connect("localhost","root","root") or die(mysql_error());

$db = @mysql_select_db($db_name, $connection) or die(mysql_error());

$logdate = mysql_query("SELECT DISTINCT whatday FROM `dinocap2hd`");

while ($everylogdate = mysql_fetch_array($logdate)) {

    //weekActivePesonrNum   每周活跃人数  SELECT * FROM dinocap2hd   where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]')
    $weekActivePesonrNum_query = mysql_query("SELECT count(DISTINCT deviceid)  FROM  dinocap2hd  where date_add(whatday,interval 2 day) <= '$everylogdate[whatday]'  and YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]')  and  isjailbreak = '0'");
    $weekActivePesonrNum = mysql_fetch_array($weekActivePesonrNum_query);

    //weekLoyalPesonrNum  每周忠诚用户人数
    $weekLoyalPesonrNum_query = mysql_query("SELECT count(DISTINCT deviceid)  FROM  dinocap2hd  where date_add(whatday,interval 3 day) <= '$everylogdate[whatday]' and  YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]')  and  isjailbreak = '0'");
    $weekLoyalPesonrNum = mysql_fetch_array($weekLoyalPesonrNum_query);
    //weekCorePesonrNum   每周核心用户人数
    $weekCorePesonrNum_query = mysql_query("SELECT count(DISTINCT deviceid)  FROM  dinocap2hd  where  date_add(whatday,interval 4 day) <= '$everylogdate[whatday]' and YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]')  and  isjailbreak = '0'");
    $weekCorePesonrNum = mysql_fetch_array($weekCorePesonrNum_query);
	//日均注册人数
	
	    $dayLoginPersonNum_query = mysql_query("select(SELECT count(DISTINCT deviceid) FROM dinocap2hd where isjailbreak = '0')/(SELECT count( DISTINCT whatday )FROM dinocap2hd where isjailbreak = '0')");

    $dayLoginPersonNum = mysql_fetch_array($dayLoginPersonNum_query);
	
	     //weekPayPesonrNum 周付费用户数 SELECT count( DISTINCT deviceid )
   // FROM dinocap2hd
    //WHERE dayIapMoney >0
    //AND YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )
    //AND isjailbreak = '0'
    $weekPayPesonrNum_query = mysql_query("SELECT count(distinct deviceid)  FROM  dinocap2hd  where dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and  isjailbreak = '0'");
    $weekPayPesonrNum = mysql_fetch_array($weekPayPesonrNum_query);

    //dayActivePesonrTime 每日活跃人次
    $dayActivePesonrTime_query = mysql_query("SELECT count(deviceid)  FROM  dinocap2hd  where  whatday ='$everylogdate[whatday]' and  isjailbreak = '0' AND dayPlayTime >2");
    $dayActivePesonrTime = mysql_fetch_array($dayActivePesonrTime_query);
 

    //  // 日均活跃人次 activePesonrTime 一个人可能多次
    $activePesonrTime_query = mysql_query("select (SELECT count(deviceid)  FROM dinocap2hd where isjailbreak = '0' AND dayPlayTime >2)/(SELECT count( DISTINCT whatday )FROM dinocap2hd)");
    $activePesonrTime = mysql_fetch_array($activePesonrTime_query);
 
    
    //weekPayARPU 周付费用户ARPU值 =周付费总额/周付费人数
    //总额
     //$weekPayNums_query = mysql_query("SELECT count(dayIapMoney)  FROM  dinocap2hd  where dayIapMoney >0 and  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]'");
       // $weekPayNums = mysql_fetch_array($weekPayNums_query);
    
   
    //$weekPayARPU_query = mysql_query("select '$weekPayNums[0]'/'$weekPayPesonrNum[0]'");
    $weekPayARPU_query = mysql_query("select (select sum(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ))/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( now( ) ) and dayIapMoney > 0)");

    $weekPayARPU = mysql_fetch_array($weekPayARPU_query);
    //weekActiveARPU 周活跃用户ARPU值
    
    //$weekActiveARPU_query = mysql_query("select '$weekPayNums[0]'/'$weekActivePesonrNum[0]'");
   $weekActiveARPU_query = mysql_query("select (select sum(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]') and isjailbreak = '0')/(select count(distinct deviceid) from dinocap2hd where  date_add(whatday,interval 2 day) <= '$everylogdate[whatday]' and YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]' ) and isjailbreak = '0' )");
   $weekActiveARPU = mysql_fetch_array($weekActiveARPU_query);
  // $weekActiveARPU = 0;
    
    //weekLogARPU 周登陆用户ARPU值  = 周付费用户/周登陆人数(活跃用户+新用户)  
    //周登陆人数
      //  $weekLodPesonrNums_query = mysql_query("SELECT count(deviceid)  FROM  dinocap2hd  where  DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(whatday) and whatday = '$everylogdate[whatday]'");
   //   $weekLodPesonrNums = 0;
    //
     $weekLogARPU_query = mysql_query("select (select count(dayIapMoney) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]') and isjailbreak = '0')/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]') and isjailbreak = '0')");
      $weekLogARPU = mysql_fetch_array($weekLogARPU_query);
    //$weekLogARPU = 0;
    //weekWastageRate  周活跃用户流失率 上周活跃用户在本周没有登陆过游戏的用户数，除以上周的活跃人数。   
       $weekWastageRate_query = mysql_query("SELECT (SELECT count( DISTINCT deviceid )FROM `dinocap2hd` WHERE (SELECT count( DISTINCT deviceid ) FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]') -1 AND dayIapMoney >0 AND dayPlayTime >2 AND isjailbreak = '0') NOT IN (SELECT DISTINCT deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]' ))AND isjailbreak = '0') / (SELECT count( DISTINCT deviceid )FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]' ) -1 AND dayIapMoney >0 AND dayPlayTime >2 AND isjailbreak = '0' ) ");
     $weekWastageRate = mysql_fetch_array($weekWastageRate_query);
   // $weekWastageRate = 0;
    //weekPayRate  周活跃用户付费率 周付费用户数除以周活跃人数
    //$weekPayRate_query = mysql_query("select '$weekPayPesonrNum[0]'/'$weekActivePesonrNum[0]'");
    $weekPayRate_query = mysql_query("select (select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) and dayIapMoney > 0 AND isjailbreak = '0')/(SELECT count(distinct deviceid) FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK('$everylogdate[whatday]')-1 and dayIapMoney >0 and dayPlayTime >2 AND isjailbreak = '0')");
    $weekPayRate = mysql_fetch_array($weekPayRate_query);
    //weekLogPayRate周登陆用户付费率 周付费用户数除以周登陆人数
    $weekLogPayRate_query = mysql_query("select (select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) and dayIapMoney > 0 AND isjailbreak = '0')/(select count(distinct deviceid) from dinocap2hd where YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) AND isjailbreak = '0')");
    $weekLogPayRate = mysql_fetch_array($weekLogPayRate_query);
   //weekPayWastageRate  周付费用户流失率 上周有付费行为而本周7天未登陆的用户数除以上周的付费用户数    //sql查询慢,需要优化
   //SELECT count(id)  FROM  dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) -1 and dayIapMoney >0 and whatday = '2012-03-28' where 
    // $weekPayWastageRate_query = mysql_query("select (select count(deviceid) FROM  dinocap2hd where (SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK('$everylogdate[whatday]')-1 and dayIapMoney >0) not in (SELECT  distinct deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' )) and whatday = '$everylogdate[whatday]')/(SELECT distinct deviceid FROM dinocap2hd   WHERE YEARWEEK(date_format(whatday,'%Y-%m-%d')) = YEARWEEK('$everylogdate[whatday]')-1 and dayIapMoney >0 and whatday = '$everylogdate[whatday]')");
      $weekPayWastageRate_query = mysql_query("SELECT (SELECT count( DISTINCT deviceid )FROM `dinocap2hd` WHERE (SELECT count( DISTINCT deviceid ) FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) -1 AND dayIapMoney >0 AND isjailbreak = '0') NOT IN (SELECT DISTINCT deviceid FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' )) AND isjailbreak = '0') / (SELECT count( DISTINCT deviceid ) FROM dinocap2hd WHERE YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) -1 AND dayIapMoney >0 AND isjailbreak = '0' ) ");
     $weekPayWastageRate = mysql_fetch_array($weekPayWastageRate_query);
    //$weekPayWastageRate  = 0;
 //   var_dump($weekPayWastageRate);die;

   //本周总充值笔数除
   // $weekpayTimes_query = mysql_query("SELECT count(id)  FROM  dinocap2hd  where  dayIapMoney >0 and  YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK('$everylogdate[whatday]') and whatday = '$everylogdate[whatday]'");
   // $weekpayTimes = mysql_fetch_array($weekpayTimes_query);
       
    //$weekPayPesonrTime_query = mysql_query("select '$weekpayTimes[0]'/'$weekPayPesonrNum[0]'");
    //$weekPayPesonrTime = mysql_fetch_array($weekPayPesonrTime_query);
    //$weekPayPesonrTime = 0; //周付费用户人均充值笔数 (本周总充值笔数除以付费用户人数)
           $weekPayPesonrTime_query = mysql_query("SELECT (SELECT sum( dayPlayTime ) FROM dinocap2hd WHERE dayIapMoney >0 AND YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) AND isjailbreak = '0' ) / ( SELECT count( DISTINCT deviceid ) FROM dinocap2hd WHERE dayIapMoney >0 AND YEARWEEK( date_format( whatday, '%Y-%m-%d' ) ) = YEARWEEK( '$everylogdate[whatday]' ) AND isjailbreak = '0' )");
    $weekPayPesonrTime = mysql_fetch_array($weekPayPesonrTime_query);
    
   // $firstlogindata =  mysql_query("SELECT DISTINCT firstlogindata whatday FROM `dinocap2hd`");

    //var_dump($dayActivePesonrNum);  //前一天select * from bean where date(usedate) = date_sub(curdate(),interval 1 day);
   /* $sql = "REPLACE INTO report (activePesonrTime,weekPayARPU,weekActiveARPU,weekLogARPU,
                          weekPayRate,weekPayPesonrTime,weekPayWastageRate,weekWastageRate,weekPayPesonrNum) 
                  VALUES('$weekPayARPU[0]','$weekActiveARPU[0]','$weekLogARPU[0]',
                  '$weekPayRate[0]','$weekPayPesonrTime[0]','$weekPayWastageRate[0]','$weekWastageRate[0]')";*/
                 $sql = "UPDATE report_nojailbreak SET weekPayARPU='$weekPayARPU[0]' ,weekActiveARPU ='$weekActiveARPU[0]',
                          weekActivePesonrNum = '$weekActivePesonrNum[0]',weekLoyalPesonrNum = '$weekLoyalPesonrNum[0]',weekCorePesonrNum = '$weekCorePesonrNum[0]',
                          weekLogARPU = '$weekLogARPU[0]',weekPayRate = '$weekPayRate[0]',weekPayPesonrTime ='$weekPayPesonrTime[0]',
                          weekPayWastageRate = '$weekPayWastageRate[0]',weekWastageRate = '$weekWastageRate[0]',activePesonrTime = '$activePesonrTime[0]',dayActivePesonrTime ='$dayActivePesonrTime[0]' ,weekPayPesonrNum = '$weekPayPesonrNum[0]',dayLoginPersonNum = '$dayLoginPersonNum[0]'
WHERE logdate='$everylogdate[0]'"; 

$query= @mysql_query($sql,$connection) or die(mysql_error());  

}
?>
